#!/usr/bin/python3
# -*- coding: utf-8 -*-
# @Time     : 2020/8/15 11:54
# @Author   : hejiang
# @File     : 中棉导入.py
# @Software : vsCode

import pymysql
import time
import datetime
import uuid
import os
import xlrd


class mysql_class:
    def __init__(self):
        '''初始化实例属性'''
        self.host = '192.168.101.27'
        self.user = 'root'
        self.passwd = '123456'
        self.db = 'zhongmian_qc'
        self.file_name = '业务单元列表打印模板.xls'
        self.file_name2 = '业务单元列表打印模板.xls'
        self.db_table = 'v_tbl_func_org'
        self.TENANT_CODE = 'T0025'

    def ljdb(self):
        '''DB连接'''
        self.conn = pymysql.connect(host=self.host,
                                    user=self.user,
                                    passwd=self.passwd,
                                    db=self.db,
                                    port=3306,
                                    charset='utf8')
        # conn = pymysql.connect(ip, username, pwd, schema,port)
        self.conn.ping(True)  #使用mysql ping来检查连接,实现超时自动重新连接
        # print(getCurrentTime(), u"MySQL DB Connect Success:",user+'@'+host+':'+str(port)+'/'+db)
        self.cur = self.conn.cursor()

    # 插入数据
    def insertData(self, table, my_dict):
        '''
        新增数据共通方法
        table 表名  str
        my_dict 列  dict
        '''
        # print(my_dict)
        # print(my_dict['jj_code'])
        cols = ', '.join(my_dict.keys())
        # values = '","'.join(my_dict.values())
        values = "','".join('%s' % v for v in my_dict.values())

        sql = "replace into %s (%s) values (%s)" % (table, cols,
                                                    "'" + values + "'")
        print(sql)
        self.cur.execute(sql)
        self.conn.commit()

    # 查询数据
    def selectData(self, table):
        '''
        table 表名 str
        '''
        sql = "select * from " + table
        r = self.cur.execute(sql)  # 返回条数

        #fetchall：获取所有的数据，默认以元祖的方式返回，如果你指定了cursorclass = pymysql.cursors.DictCursor，则以dict的方式返回
        result = self.cur.fetchall()
        for row in result:
            print(row)

    # 删除数据
    def deleteData(self):
        sql = "delete from " + self.db_table
        print(sql)
        n = self.cur.execute(sql)
        print(n)

        self.conn.commit()

    # 机构字典
    def get_org_code_dict(self):
        sql = "select NAME,CODE from v_tbl_func_org"
        r = self.cur.execute(sql)  # 返回条数
        org_result = self.cur.fetchall()
        # print(org_result)

        self.org_code_dict = {}
        for org in org_result:
            self.org_code_dict[org[0]] = org[1]
        # print(self.org_code_dict)

    # 部门字典
    def get_dept_code_dict(self):
        # sql = "select concat(o.name,'-',d.NAME) as name,d.CODE from v_tbl_func_dept d left join v_tbl_func_org o on o.code = ORG_CODE "
        sql = "select NAME,CODE from v_tbl_func_dept"
        r = self.cur.execute(sql)  # 返回条数
        dept_result = self.cur.fetchall()
        # print(dept_result)

        self.dept_code_dict = {}
        for dept in dept_result:
            self.dept_code_dict[dept[0]] = dept[1]
        # print(self.dept_code_dict)

    # 机构-部门字典
    def get_org_dept_code_dict(self):
        sql = "select concat(o.name,'-',d.NAME) as name,d.CODE from v_tbl_func_dept d left join v_tbl_func_org o on o.code = ORG_CODE "
        # sql = "select NAME,CODE from v_tbl_func_dept"
        r = self.cur.execute(sql)  # 返回条数
        org_dept_result = self.cur.fetchall()
        # print(org_dept_result)

        self.org_dept_code_dict = {}
        for org_dept in org_dept_result:
            self.org_dept_code_dict[org_dept[0]] = org_dept[1]
        # print(self.org_dept_code_dict)

    # 银行类别字典
    def get_back_type_dict(self):

        sql = "select DICT_ITEM_NAME,DICT_ITEM_VAL from v_tbl_sys_dict_item where DICT_CODE = 'BANK_TYPE' and DELETE_FLAG = '0'"
        r = self.cur.execute(sql)  # 返回条数
        bank_type_result = self.cur.fetchall()
        # print(bank_type_result)

        self.back_type_dict = {}
        for back_type in bank_type_result:
            self.back_type_dict[back_type[0]] = back_type[1]
        # print(self.back_type_dict)

    # 银行账号类别字典
    def get_BANK_ACCOUNT_TYPE_dict(self):

        sql = "select DICT_ITEM_NAME,DICT_ITEM_VAL from v_tbl_sys_dict_item where DICT_CODE = 'BANK_ACCOUNT_TYPE' and DELETE_FLAG = '0'"
        r = self.cur.execute(sql)  # 返回条数
        result = self.cur.fetchall()
        # print(result)

        self.BANK_ACCOUNT_TYPE_dict = {}
        for re in result:
            self.BANK_ACCOUNT_TYPE_dict[re[0]] = re[1]
        # print(self.BANK_ACCOUNT_TYPE_dict)

    # 银行账号类别字典
    def get_CURRENCY_dict(self):

        sql = "select DICT_ITEM_NAME,DICT_ITEM_VAL from v_tbl_sys_dict_item where DICT_CODE = 'CURRENCY' and DELETE_FLAG = '0'"
        r = self.cur.execute(sql)  # 返回条数
        result = self.cur.fetchall()
        # print(result)

        self.CURRENCY_dict = {}
        for re in result:
            self.CURRENCY_dict[re[0]] = re[1]
        # print(self.CURRENCY_dict)

    # 银行编码字典
    def get_back_code_dict(self):

        sql = "select BANK_NAME,BANK_CODE from v_tbl_dd_bank"
        r = self.cur.execute(sql)  # 返回条数
        bank_code_result = self.cur.fetchall()
        # print(bank_code_result)

        self.back_code_dict = {}
        for back_code in bank_code_result:
            self.back_code_dict[back_code[0]] = back_code[1]
        # print(self.back_code_dict)

    # 员工-部门字典
    def get_employee_dept_dict(self):

        sql = "select name,DEPT_CODE from V_TBL_FUNC_EMPLOYEE"
        r = self.cur.execute(sql)  # 返回条数
        employee_dept_result = self.cur.fetchall()
        # print(employee_dept_result)

        self.employee_dept_dict = {}
        for employee_dept in employee_dept_result:
            self.employee_dept_dict[employee_dept[0]] = employee_dept[1]
        # print(self.employee_dept_dict)

    # 员工代码字典
    def get_employee_code_dict(self):

        sql = "select name,code from V_TBL_FUNC_EMPLOYEE"
        r = self.cur.execute(sql)  # 返回条数
        employee_code_result = self.cur.fetchall()
        # print(employee_code_result)

        self.employee_code_dict = {}
        for employee_code in employee_code_result:
            self.employee_code_dict[employee_code[0]] = employee_code[1]
        # print(self.employee_code_dict)

    # 轧花厂&部门 字典
    def get_FUNC_ORG_dict(self):

        # sql = "SELECT FO.name, FO.code FROM V_TBL_FUNC_ORG FO WHERE FO.DELETE_FLAG = '0' "
        sql = "SELECT FO.name, FO.code FROM V_TBL_FUNC_ORG FO "
        r = self.cur.execute(sql)  # 返回条数
        FUNC_ORG_result = self.cur.fetchall()
        # print(FUNC_ORG_result)

        self.FUNC_ORG_dict = {}
        for FUNC_ORG in FUNC_ORG_result:
            self.FUNC_ORG_dict[FUNC_ORG[0]] = FUNC_ORG[1]

        # print(self.FUNC_ORG_dict)

    # 客户 字典
    def get_cust_dict(self):

        sql = "select CUST_NAME,CUST_CODE from v_tbl_cust_info"
        r = self.cur.execute(sql)  # 返回条数
        result = self.cur.fetchall()
        # print(result)

        self.cust_dict = {}
        for re in result:
            self.cust_dict[re[0]] = re[1]

        # print(self.cust_dict)

    # 银行 字典
    def get_bank_dict(self):

        sql = "select BANK_NAME,BANK_CODE from v_tbl_dd_bank"
        r = self.cur.execute(sql)  # 返回条数
        result = self.cur.fetchall()
        # print(result)

        self.bank_dict = {}
        for re in result:
            self.bank_dict[re[0]] = re[1]

        # print(self.bank_dict)

    # 获取execl数据
    def getExeclData(self):
        self.get_org_code_dict()
        self.get_dept_code_dict()
        self.get_back_type_dict()
        self.get_back_code_dict()
        self.get_employee_dept_dict()
        self.get_employee_code_dict()
        self.get_org_dept_code_dict()
        self.get_FUNC_ORG_dict()
        self.get_cust_dict()
        self.get_bank_dict()
        self.get_BANK_ACCOUNT_TYPE_dict()

        if self.db_table == 'v_tbl_cust_info':
            self.getExeclData2()
        else:

            # 读取数据
            # 打开文件
            wb = xlrd.open_workbook(self.file_name)
            # 查看工作表
            sheet_names = wb.sheet_names()
            # print(sheet_names)
            for sheet_name in sheet_names:
                sh = wb.sheet_by_name(sheet_name)
                # print(sh)

                titles = []
                for rowNum in range(sh.nrows):
                    rowVale = sh.row_values(rowNum)

                    if rowNum == 0:
                        titles = rowVale
                    else:
                        data = rowVale
                        # print(data)
                        result = {}
                        # print(titles)
                        for j in range(len(titles)):
                            result[titles[j]] = data[j]
                        # print(result)
                        # 机构
                        if self.db_table == 'v_tbl_func_org':
                            self.doDataOrg(sh, result)
                        elif self.db_table == 'v_tbl_func_dept':
                            self.doDataDept(sh, result)
                        elif self.db_table == 'v_tbl_dd_area':
                            self.doDataArea(sh, result)
                        # elif self.db_table == 'v_tbl_cust_info':
                        #     self.doDataCust(sh, result)
                        elif self.db_table == 'v_tbl_dd_warehouse':
                            self.doDataWarehouse(sh, result)
                        elif self.db_table == 'v_tbl_dd_product_group':
                            self.doDataProduct_group(sh, result)
                        elif self.db_table == 'v_tbl_dd_product':
                            self.doDataProduct(sh, result)
                        elif self.db_table == 'V_TBL_FUNC_EMPLOYEE':
                            self.doDataEmployee(sh, result)
                        elif self.db_table == 'V_TBL_FUNC_USER':
                            self.doDataUser(sh, result)
                        elif self.db_table == 'v_tbl_dd_bank':
                            self.doDataBank(sh, result)
                        elif self.db_table == 'v_tbl_dd_organ_bank_account':
                            self.doDataBankAccount(sh, result)
                        elif self.db_table == 'ZM_TBL_DD_GIN_PLANT_ASS_SET':
                            self.doDataGinPlantAssSet(sh, result)
                        elif self.db_table == 'v_tbl_cust_bankinfo':
                            self.doDataCustBankinfo(sh, result)

    # 客户银行
    def doDataCustBankinfo(self, sh, result):

        my_dict = {}
        my_dict['id'] = str(uuid.uuid1())
        my_dict['create_date'] = self.getCurrentTime()
        my_dict['create_user'] = 'python'
        my_dict['TENANT_CODE'] = self.TENANT_CODE

        my_dict['CUST_CODE'] = result['供应商编码']
        # if result['供应商名称'] in self.cust_dict:
        #     my_dict['CUST_CODE'] = self.cust_dict[result['供应商名称']]

        my_dict['BANK_ACCOUNT_NO'] = str(result['账号']).split('.')[0]
        my_dict['BANK_ACCOUNT_NAME'] = result['账号']

        if result['开户银行'] in self.bank_dict:
            my_dict['BANK_CODE'] = self.bank_dict[result['开户银行']]

        if result['银行类别'] in self.back_type_dict:
            my_dict['BANK_TYPE'] = self.back_type_dict[result['银行类别']]
        # if result['币种'] in self.CURRENCY_dict:
        #     my_dict['CURRENCY'] = self.CURRENCY_dict[result['币种']]

        if '美元' in self.file_name:
            my_dict['CURRENCY'] = 'USD'
        else:
            my_dict['CURRENCY'] = 'CNY'
        # # print(my_dict)
        self.insertData(self.db_table, my_dict)

    def doDataGinPlantAssSet(self, sh, result):

        my_dict = {}
        my_dict['id'] = str(uuid.uuid1())
        my_dict['create_date'] = self.getCurrentTime()
        my_dict['create_user'] = 'python'
        my_dict['TENANT_CODE'] = self.TENANT_CODE

        # select WAREHOUSE_CODE,REGION_DIVIDE,XINJIANG_REGION,DIVISION_FLG,LEASE_FLG from zm_tbl_dd_gin_plant_ass_set
        my_dict['WAREHOUSE_CODE'] = 'L0002'  # 仓库
        my_dict['REGION_DIVIDE'] = '1'  # 区域划分
        my_dict['XINJIANG_REGION'] = '1'  # 新疆区域
        my_dict['DIVISION_FLG'] = '1'  # 所属组织

        if result['(主体)一级'] == '租赁':
            my_dict['LEASE_FLG'] = '1'  # 租赁区分
        else:
            my_dict['LEASE_FLG'] = '2'  # 租赁区分

        GINNING_PLANT_NO = str(result['企业编码']).split('.')[0].split('-')[0]
        if len(GINNING_PLANT_NO) != 0 and GINNING_PLANT_NO != '主体':
            my_dict['GINNING_PLANT_NO'] = GINNING_PLANT_NO

            GINNING_PLANT_CODE = result['轧花厂/部门']
            if len(GINNING_PLANT_CODE) == 0:
                GINNING_PLANT_CODE = result['(主体)二级']

            if GINNING_PLANT_CODE not in self.FUNC_ORG_dict:
                codeFlag = False
                for k in self.FUNC_ORG_dict.keys():
                    # print(k)
                    if GINNING_PLANT_CODE in k:
                        GINNING_PLANT_CODE = k
                        codeFlag = True
                        break

                # if not codeFlag:
                #     print(GINNING_PLANT_CODE)

                if codeFlag:
                    my_dict['GINNING_PLANT_CODE'] = self.FUNC_ORG_dict[
                        GINNING_PLANT_CODE]
            elif GINNING_PLANT_CODE in self.FUNC_ORG_dict:
                my_dict['GINNING_PLANT_CODE'] = self.FUNC_ORG_dict[
                    GINNING_PLANT_CODE]

            # # print(my_dict)
            self.insertData(self.db_table, my_dict)

    # 银行账号
    def doDataBankAccount(self, sh, result):

        my_dict = {}
        my_dict['id'] = str(uuid.uuid1())
        my_dict['USING_FLAG'] = '1'
        my_dict['create_date'] = self.getCurrentTime()
        my_dict['create_user'] = 'python'
        my_dict['TENANT_CODE'] = self.TENANT_CODE

        my_dict['BANK_ACCOUNT_TYPE'] = '1'
        my_dict['CURRENCY'] = 'CNY'

        orgName = result['所属组织']
        if orgName == '中华棉花集团':
            orgName = '中华棉花集团有限公司'

        if orgName in self.org_code_dict:
            my_dict['ORGAN_CODE'] = self.org_code_dict[orgName]

        if result['开户银行'] in self.back_code_dict:
            my_dict['BANK_CODE'] = self.back_code_dict[result['开户银行']]

        my_dict['BANK_ACCOUNT_CODE'] = str(result['账户编码']).split('.')[0]
        my_dict['BANK_ACCOUNT_NO'] = str(result['账号']).split('.')[0]

        my_dict['BANK_ACCOUNT_NAME'] = result['账户名称']
        if result['银行类别'] in self.back_type_dict:
            my_dict['BANK_TYPE'] = self.back_type_dict[result['银行类别']]

        if result['账户性质'] in self.BANK_ACCOUNT_TYPE_dict:
            my_dict['ACCOUNT_TYPE'] = self.BANK_ACCOUNT_TYPE_dict[
                result['账户性质']]

        # print(my_dict)
        self.insertData(self.db_table, my_dict)

    # 银行档案
    def doDataBank(self, sh, result):

        my_dict = {}
        my_dict['id'] = str(uuid.uuid1())
        my_dict['USING_FLAG'] = '1'
        my_dict['create_date'] = self.getCurrentTime()
        my_dict['create_user'] = 'python'
        my_dict['TENANT_CODE'] = self.TENANT_CODE

        my_dict['BANK_CODE'] = str(result['银行编码']).split('.')[0]
        my_dict['BANK_NAME'] = result['银行名称']
        if result['银行类别'] in self.back_type_dict:
            my_dict['BANK_TYPE'] = self.back_type_dict[result['银行类别']]

        # print(my_dict)
        self.insertData(self.db_table, my_dict)

    # 用户
    def doDataUser(self, sh, result):

        my_dict = {}
        my_dict['id'] = str(uuid.uuid1())
        my_dict['USING_FLAG'] = '1'
        my_dict['create_date'] = self.getCurrentTime()
        my_dict['create_user'] = 'python'
        my_dict['TENANT_CODE'] = self.TENANT_CODE

        my_dict['ACCOUNT'] = str(result['用户编码']).split('.')[0]
        my_dict['CODE'] = str(result['用户编码']).split('.')[0]
        my_dict['NAME'] = result['用户名称']
        # my_dict['PASSWORD'] = '123456'
        # 加密
        my_dict['PASSWORD'] = '71fa6e20241cc1ae7cf351c37c0bc4fe'
        my_dict['SALT'] = 'exE4Xw'

        my_dict['USER_VALID_PERIOD_FLAG'] = '1'
        my_dict['PASSWD_VALID_PERIOD_FLAG'] = '1'
        EMPLOYEE = result['身份']

        if EMPLOYEE in self.employee_code_dict:
            my_dict['EMPLOYEE_CODE'] = self.employee_code_dict[EMPLOYEE]

        # my_dict['EMPLOYEE_CODE'] = result['身份']

        my_dict['IDENTITY'] = '4'
        my_dict['DEPT_CODE'] = '待定'

        orgName = result['所属组织']
        if orgName == '中华棉花集团':
            orgName = '中华棉花集团有限公司'

        if orgName in self.org_code_dict:
            my_dict['ORG_CODE'] = self.org_code_dict[orgName]

        if EMPLOYEE in self.employee_dept_dict:
            my_dict['DEPT_CODE'] = self.employee_dept_dict[EMPLOYEE]
            # print(my_dict)
            self.insertData(self.db_table, my_dict)

    # # 人员    弃用
    # def doDataEmployee(self, sh, result):

    #     my_dict = {}
    #     my_dict['id'] = str(uuid.uuid1())
    #     my_dict['USING_FLAG'] = '1'
    #     my_dict['create_date'] = self.getCurrentTime()
    #     my_dict['create_user'] = 'python'
    #     my_dict['TENANT_CODE'] = self.TENANT_CODE

    #     my_dict['CODE'] = str(result['人员编码']).split('.')[0]
    #     my_dict['NAME'] = result['姓名']
    #     if len(result['性别']) == 0:
    #         my_dict['GENDER'] = '冇'
    #     else:
    #         my_dict['GENDER'] = result['性别']

    #     if result['所属组织'] in self.org_code_dict:
    #         my_dict['ORG_CODE'] = self.org_code_dict[result['所属组织']]

    #     # org_dept = result['所属组织'] + '-' + result['所在部门']

    #     org_dept = result['所在部门']
    #     if org_dept in self.dept_code_dict:
    #         my_dict['DEPT_CODE'] = self.dept_code_dict[org_dept]
    #         self.insertData(self.db_table, my_dict)

    # 人员
    def doDataEmployee(self, sh, result):

        my_dict = {}
        my_dict['id'] = str(uuid.uuid1())
        my_dict['USING_FLAG'] = '1'
        my_dict['create_date'] = self.getCurrentTime()
        my_dict['create_user'] = 'python'
        my_dict['TENANT_CODE'] = self.TENANT_CODE

        my_dict['CODE'] = str(result['人员编码']).split('.')[0].replace("'", "")
        my_dict['NAME'] = result['姓名']
        if str(result['性别']).split('.')[0] == '1':
            my_dict['GENDER'] = 'M'
        else:
            my_dict['GENDER'] = 'F'

        if result['所属公司'] in self.org_code_dict:
            my_dict['ORG_CODE'] = self.org_code_dict[result['所属公司']]

        # org_dept = result['所属部门']
        # if org_dept in self.dept_code_dict:
        #     my_dict['DEPT_CODE'] = self.dept_code_dict[org_dept]
        #     self.insertData(self.db_table, my_dict)

        org_dept = result['所属公司'] + '-' + result['所属部门']
        if org_dept in self.org_dept_code_dict:
            my_dict['DEPT_CODE'] = self.org_dept_code_dict[org_dept]
            self.insertData(self.db_table, my_dict)

    # 物料
    def doDataProduct(self, sh, result):

        sql = "select PRODUCT_GROUP_NAME,PRODUCT_GROUP_CODE from v_tbl_dd_product_group"
        r = self.cur.execute(sql)  # 返回条数
        pro_group_result = self.cur.fetchall()
        # print(pro_group_result)

        pro_group_code_dict = {}
        for group in pro_group_result:
            pro_group_code_dict[group[0]] = group[1]

        my_dict = {}
        my_dict['id'] = str(uuid.uuid1())
        my_dict['USING_FLAG'] = '1'
        my_dict['RESERVED_STR2'] = '0'
        my_dict['create_date'] = self.getCurrentTime()
        my_dict['create_user'] = 'python'
        my_dict['TENANT_CODE'] = self.TENANT_CODE

        PRODUCT_CODE = str(result['物料编码']).split('.')[0]
        my_dict['PRODUCT_CODE'] = PRODUCT_CODE
        my_dict['PRODUCT_NAME'] = result['物料名称']
        # my_dict['MEASUREMENT_UNIT_CODE'] = result['主计量单位']
        my_dict['MEASUREMENT_UNIT_CODE'] = 'TNE'
        if PRODUCT_CODE[:4] == '0102':  # 皮棉辅单位：包
            my_dict['PRODUCT_UNIT_CODE'] = 'BALE'
        else:
            my_dict['PRODUCT_UNIT_CODE'] = 'JIAN'  # 其他的，暂时随便设定一个

        if result['物料分类'] in pro_group_code_dict:
            my_dict['PRODUCT_GROUP_CODE'] = pro_group_code_dict[result['物料分类']]

        if '籽棉' in result['物料分类']:
            my_dict['REPO_TYPE'] = 'SEED'
        elif '进口' in result['物料分类'] and '棉' in result['物料分类']:
            my_dict['REPO_TYPE'] = 'IMPORT'
        elif '内采' in result['物料分类']:
            my_dict['REPO_TYPE'] = 'IMPORT'
        elif '棉' == result['物料分类'][-1:]:
            my_dict['REPO_TYPE'] = 'DOMESTIC'
        else:
            my_dict['REPO_TYPE'] = 'OTHERS'

        type_pro = result['物料分类']
        if '绒棉' in type_pro:
            if '新疆' in type_pro or '地产' in type_pro:
                if '细' in type_pro:
                    my_dict['PRODUCT_COLLECT_CODE'] = 'D1E1F1'
                elif '中' in type_pro:
                    my_dict['PRODUCT_COLLECT_CODE'] = 'D1E1F2'
                elif '长' in type_pro:
                    my_dict['PRODUCT_COLLECT_CODE'] = 'D1E1F3'
                else:
                    my_dict['PRODUCT_COLLECT_CODE'] = 'D1E1'

            elif '进口' in type_pro:
                if '细' in type_pro:
                    my_dict['PRODUCT_COLLECT_CODE'] = 'D1E2F1'
                elif '中' in type_pro:
                    my_dict['PRODUCT_COLLECT_CODE'] = 'D1E2F2'
                elif '长' in type_pro:
                    my_dict['PRODUCT_COLLECT_CODE'] = 'D1E2F3'
                else:
                    my_dict['PRODUCT_COLLECT_CODE'] = 'D1E2'
        elif '棉籽' in type_pro:
            if '新疆' in type_pro or '地产' in type_pro:
                my_dict['PRODUCT_COLLECT_CODE'] = 'D2E1'
            elif '进口' in type_pro:
                my_dict['PRODUCT_COLLECT_CODE'] = 'D2E2'
            else:
                my_dict['PRODUCT_COLLECT_CODE'] = 'D2'
        elif '短绒' in type_pro:
            if '新疆' in type_pro or '地产' in type_pro:
                my_dict['PRODUCT_COLLECT_CODE'] = 'D3E1'
            elif '进口' in type_pro:
                my_dict['PRODUCT_COLLECT_CODE'] = 'D3E2'
            else:
                my_dict['PRODUCT_COLLECT_CODE'] = 'D3'
        elif '化纤原料' in type_pro:
            my_dict['PRODUCT_COLLECT_CODE'] = 'D5'
        elif '化纤' in type_pro:
            if '新疆' in type_pro or '地产' in type_pro:
                my_dict['PRODUCT_COLLECT_CODE'] = 'D4E1'
            elif '进口' in type_pro:
                my_dict['PRODUCT_COLLECT_CODE'] = 'D4E2'
            else:
                my_dict['PRODUCT_COLLECT_CODE'] = 'D4'
        elif '棉纱' in type_pro:
            if '新疆' in type_pro or '地产' in type_pro:
                my_dict['PRODUCT_COLLECT_CODE'] = 'D6E1'
            elif '进口' in type_pro:
                my_dict['PRODUCT_COLLECT_CODE'] = 'D6E2'
            else:
                my_dict['PRODUCT_COLLECT_CODE'] = 'D6'
        else:
            my_dict['PRODUCT_COLLECT_CODE'] = 'D7'

        my_dict['ALLOCATION_TYPE'] = '0'
        # 基准价格（暂定）
        my_dict['PRICE_ITEM_CODE'] = '00000001'

        # print(my_dict)
        self.insertData(self.db_table, my_dict)

        #         v_tbl_dd_product_item

        my_dict = {}
        my_dict['id'] = str(uuid.uuid1())
        my_dict['USING_FLAG'] = '1'
        my_dict['create_date'] = self.getCurrentTime()
        my_dict['create_user'] = 'python'
        my_dict['TENANT_CODE'] = self.TENANT_CODE
        my_dict['PRODUCT_CODE'] = str(result['物料编码']).split('.')[0]
        my_dict['PRODUCT_ITEM_CODE'] = str(result['物料编码']).split('.')[0]
        my_dict['RESERVED_STR1'] = result['物料名称']

        self.insertData('v_tbl_dd_product_item', my_dict)

        # v_tbl_dd_product_tax_rate
        my_dict = {}
        my_dict['id'] = str(uuid.uuid1())
        my_dict['create_date'] = self.getCurrentTime()
        my_dict['create_user'] = 'python'
        my_dict['TENANT_CODE'] = self.TENANT_CODE

        my_dict['PRODUCT_CODE'] = str(result['物料编码']).split('.')[0]
        my_dict['TAX_TYPE'] = '1'
        my_dict['BEGIN_DATE'] = '20200801'
        my_dict['TAX_RATE'] = '13'
        if '010203020111' != my_dict[
                'PRODUCT_CODE'] and '010201030111' != my_dict['PRODUCT_CODE']:
            self.insertData('v_tbl_dd_product_tax_rate', my_dict)

    # 物料分类
    def doDataProduct_group(self, sh, result):

        my_dict = {}
        my_dict['id'] = str(uuid.uuid1())
        # my_dict['USING_FLAG'] = '1'
        my_dict['create_date'] = self.getCurrentTime()
        my_dict['create_user'] = 'python'
        my_dict['TENANT_CODE'] = self.TENANT_CODE

        my_dict['PRODUCT_GROUP_CODE'] = str(result['物料基本分类编码']).split('.')[0]
        my_dict['PRODUCT_GROUP_NAME'] = result['物料基本分类名称']
        my_dict['PARENT_CODE'] = str(result['物料基本分类编码']).split('.')[0][:-2]

        # print(my_dict)
        self.insertData(self.db_table, my_dict)

    # 机构
    def doDataOrg(self, sh, result):

        nameList = [sh.cell(i, 1).value for i in range(1, sh.nrows)]
        codeList = [
            str(sh.cell(i, 0).value).split('.')[0] for i in range(1, sh.nrows)
        ]
        # print(codeList)

        self.org_code_dict = {}
        for i in range(len(nameList)):
            self.org_code_dict[nameList[i]] = codeList[i]
        # print(self.org_code_dict)

        my_dict = {}
        my_dict['id'] = str(uuid.uuid1())
        my_dict['USING_FLAG'] = '1'
        my_dict['RESERVED_STR2'] = '0'
        my_dict['create_date'] = self.getCurrentTime()
        my_dict['create_user'] = 'python'
        my_dict['TENANT_CODE'] = self.TENANT_CODE
        my_dict['CODE'] = str(result['编码']).split('.')[0]
        my_dict['NAME'] = result['名称']
        my_dict['ABBR'] = result['简称']
        my_dict['TITLE'] = result['名称']
        if result['上级业务单元'] in self.org_code_dict:
            my_dict['PARENT_CODE'] = self.org_code_dict[result['上级业务单元']]
        if result['本位币'] == '人民币':
            my_dict['RESERVED_STR3'] = 'CNY'
        elif result['本位币'] == '美元':
            my_dict['RESERVED_STR3'] = 'USD'

        # print(my_dict)
        self.insertData(self.db_table, my_dict)

    # 部门
    def doDataDept(self, sh, result):

        nameList = [sh.cell(i, 2).value for i in range(1, sh.nrows)]
        codeList = [
            str(sh.cell(i, 1).value).split('.')[0] for i in range(1, sh.nrows)
        ]
        # print(codeList)

        dept_code_dict = {}
        for i in range(len(nameList)):
            dept_code_dict[nameList[i]] = codeList[i]
        # print(dept_code_dict)

        my_dict = {}
        my_dict['id'] = str(uuid.uuid1())
        my_dict['USING_FLAG'] = '1'
        my_dict['create_date'] = self.getCurrentTime()
        my_dict['create_user'] = 'python'
        my_dict['TENANT_CODE'] = self.TENANT_CODE

        # print(result)
        my_dict['CODE'] = str(result['编码']).split('.')[0]
        my_dict['NAME'] = result['名称']
        if result['所属公司'] in self.org_code_dict:
            my_dict['ORG_CODE'] = self.org_code_dict[result['所属公司']]

        if result['上级部门'] in dept_code_dict:
            my_dict['PARENT_CODE'] = dept_code_dict[result['上级部门']]
        my_dict['PRINCIPAL'] = result['负责人']
        my_dict['PHONE'] = result['电话']
        my_dict['RESERVED_STR1'] = result['简称']
        my_dict['RESERVED_STR2'] = '0'
        # print(my_dict)
        self.insertData(self.db_table, my_dict)

    # 地区
    def doDataArea(self, sh, result):

        my_dict = {}
        my_dict['id'] = str(uuid.uuid1())
        my_dict['create_date'] = self.getCurrentTime()
        my_dict['create_user'] = 'python'
        my_dict['TENANT_CODE'] = self.TENANT_CODE

        my_dict['AREA_CODE'] = str(result['编码']).split('.')[0]
        my_dict['AREA_NAME'] = result['名称']
        # my_dict['PARENT_CODE'] = result['']

        # print(my_dict)
        self.insertData(self.db_table, my_dict)

    # 获取execl数据
    def getExeclData2(self):

        # 打开文件
        wb = xlrd.open_workbook(self.file_name)
        sh = wb.sheet_by_index(0)

        cus_codeList = [
            str(sh.cell(i, 1).value).split('.')[0] for i in range(1, sh.nrows)
        ]

        titles = []
        for rowNum in range(sh.nrows):
            rowVale = sh.row_values(rowNum)
            # print(rowVale)

            if rowNum == 0:
                titles = rowVale
            else:
                data = rowVale
                # print(data)
                result = {}
                # print(titles)
                for j in range(len(titles)):
                    result[titles[j]] = data[j]
                # print(result)
                my_dict = {}
                my_dict['id'] = str(uuid.uuid1())
                my_dict['create_date'] = self.getCurrentTime()
                my_dict['create_user'] = 'python'
                my_dict['TENANT_CODE'] = self.TENANT_CODE

                my_dict['AUDIT_STATUS'] = '3'
                my_dict['TYPE_CODE'] = '1'
                my_dict['ENTERPRISE_TYPE_CODE'] = '2'
                my_dict['ENTERPRISE_FORM_CODE'] = '2'
                my_dict['USING_FLAG'] = '1'
                my_dict['IS_BLACKLIST'] = '0'
                my_dict['CUST_LEVEL'] = '0'
                my_dict['CURRENCY'] = 'CNY'

                my_dict['CUST_CODE'] = str(result['客户编码']).split('.')[0]
                my_dict['CUST_NAME'] = result['客户名称']
                my_dict['CUST_FULLNAME_CHS'] = result['客户简称']
                my_dict['CUST_HEADLINE'] = result['客户名称']
                if result['客户类型'] == '外部单位':
                    my_dict['REVERSE_FLAG'] = '0'
                elif result['客户类型'] == '内部单位':
                    my_dict['REVERSE_FLAG'] = '1'

                if result['对应业务单元'] in self.org_code_dict:
                    my_dict['REVERSE_ORGAN_CODE'] = self.org_code_dict[
                        result['对应业务单元']]
                # print(my_dict)
                self.insertData(self.db_table, my_dict)

        # 打开文件
        wb = xlrd.open_workbook(self.file_name2)
        sh = wb.sheet_by_index(0)
        titles = []
        supplier_list = []
        for rowNum in range(sh.nrows):
            rowVale = sh.row_values(rowNum)
            # print(rowVale)

            if rowNum == 0:
                titles = rowVale
            else:
                data = rowVale
                # print(data)
                result = {}
                # print(titles)
                for j in range(len(titles)):
                    result[titles[j]] = data[j]
                # print(result)
                CUST_CODE = str(result['供应商编码']).split('.')[0]

                # sql = "select * from v_tbl_cust_info where CUST_CODE = '" + CUST_CODE + "'"
                # num = self.cur.execute(sql)  # 返回条数

                # if num == 0:
                if CUST_CODE not in cus_codeList:  # 存在更新TYPE_CODE，不存在新增
                    my_dict = {}
                    my_dict['id'] = str(uuid.uuid1())
                    my_dict['create_date'] = self.getCurrentTime()
                    my_dict['create_user'] = 'python'
                    my_dict['TENANT_CODE'] = self.TENANT_CODE
                    my_dict['AUDIT_STATUS'] = '3'
                    my_dict['CUST_CODE'] = CUST_CODE
                    my_dict['CUST_NAME'] = result['供应商名称']
                    my_dict['CUST_FULLNAME_CHS'] = result['供应商简称']
                    my_dict['CUST_HEADLINE'] = result['供应商名称']
                    if result['供应商类型'] == '外部单位':
                        my_dict['REVERSE_FLAG'] = '0'
                    elif result['供应商类型'] == '内部单位':
                        my_dict['REVERSE_FLAG'] = '1'

                    if result['对应业务单元'] in self.org_code_dict:
                        my_dict['REVERSE_ORGAN_CODE'] = self.org_code_dict[
                            result['对应业务单元']]
                    my_dict['TYPE_CODE'] = '2'
                    # print(my_dict)
                    self.insertData(self.db_table, my_dict)
                else:
                    # my_dict['TYPE_CODE'] = '12'
                    supplier_list.append('CUST_CODE')

        count = 500
        num = (len(supplier_list) //
               count) if (len(supplier_list) %
                          count) == 0 else (len(supplier_list) // count) + 1
        for i in range(num):
            split_list = list[i * count:(i + 1) * count]
            spilt_CUST_CODE = "','".join('%s' % v for v in split_list)
            sql = "update v_tbl_cust_info set TYPE_CODE = '12' where CUST_CODE in ('" + spilt_CUST_CODE + "')"
            print(sql)
            self.cur.execute(sql)
            self.conn.commit()

    # 仓库
    def doDataWarehouse(self, sh, result):
        cust_dict = {}
        custId = str(uuid.uuid1())
        cust_dict['id'] = custId
        cust_dict['create_date'] = self.getCurrentTime()
        cust_dict['create_user'] = 'python'
        cust_dict['TENANT_CODE'] = self.TENANT_CODE
        cust_dict['AUDIT_STATUS'] = '3'
        cust_dict['ENTERPRISE_TYPE_CODE'] = '2'
        cust_dict['ENTERPRISE_FORM_CODE'] = '2'
        cust_dict['USING_FLAG'] = '1'
        cust_dict['IS_BLACKLIST'] = '0'
        cust_dict['REVERSE_FLAG'] = '0'
        cust_dict['TYPE_CODE'] = '3'
        cust_dict['CUST_LEVEL'] = '0'
        cust_dict['CREDIT_RATING'] = 'AA'
        cust_dict['CURRENCY'] = 'CNY'
        cust_dict['CUST_CODE'] = str(result['仓库编码']).split('.')[0]
        cust_dict['CUST_NAME'] = result['仓库名称']
        cust_dict['CUST_FULLNAME_CHS'] = result['仓库名称']
        cust_dict['CUST_HEADLINE'] = result['仓库名称']
        self.insertData('v_tbl_cust_info', cust_dict)

        my_dict = {}
        my_dict['id'] = str(uuid.uuid1())
        my_dict['create_date'] = self.getCurrentTime()
        my_dict['create_user'] = 'python'
        my_dict['TENANT_CODE'] = self.TENANT_CODE
        my_dict['WAREHOUSE_TYPE'] = 'T'
        my_dict['USING_FLAG'] = '1'
        if '保税库' in result['仓库名称']:
            my_dict['BONDED_FLAG'] = '1'

        my_dict['AREA_TYPE'] = '2'
        my_dict['AREA_CODE'] = 'CN'
        my_dict['ALLOCATION_TYPE'] = '0'
        my_dict['WAREHOUSE_CODE'] = str(result['仓库编码']).split('.')[0]
        my_dict['WAREHOUSE_NAME'] = result['仓库名称']
        my_dict['STORE_COMPANY_CODE'] = str(result['仓库编码']).split('.')[0]
        my_dict['CONTACT'] = result['负责人']
        my_dict['TEL'] = result['电话号码']
        my_dict['ADDRESS'] = result['仓库地址']

        # print(my_dict)
        self.insertData(self.db_table, my_dict)

    # 获取当前时间
    def getCurrentTime(self):
        return time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(time.time()))

    # 机构  创建函数并更新levelCode
    def updateOrgLevelCode(self):
        sql = "DROP FUNCTION IF EXISTS get_org_level_code;"
        self.cur.execute(sql)
        self.conn.commit()

        sql = ""
        sql += " CREATE FUNCTION get_org_level_code ( orgCode VARCHAR ( 256 ) ) RETURNS VARCHAR ( 256 ) BEGIN          "
        sql += " DECLARE                                                                                               "
        sql += " 	temp_level_code VARCHAR ( 256 );                                                                   "
        sql += " DECLARE                                                                                               "
        sql += " 	temp_parent_code VARCHAR ( 256 );                                                                  "
        sql += " SET temp_level_code = CAST( orgCode AS CHAR );                                                        "
        sql += " SET temp_parent_code = '';                                                                            "
        sql += " SELECT                                                                                                "
        sql += " 	o.parent_code INTO temp_parent_code                                                                "
        sql += " FROM                                                                                                  "
        sql += " 	v_tbl_func_org o                                                                                   "
        sql += " WHERE                                                                                                 "
        sql += " 	o.CODE = orgCode;                                                                                  "
        sql += " WHILE                                                                                                 "
        sql += " 	temp_parent_code IS NOT NULL DO                                                                    "
        sql += " SET temp_level_code = CONCAT( temp_parent_code, '-', temp_level_code );                               "
        sql += " SELECT                                                                                                "
        sql += " 	o.parent_code INTO temp_parent_code                                                                "
        sql += " FROM                                                                                                  "
        sql += " 	v_tbl_func_org o                                                                                   "
        sql += " WHERE                                                                                                 "
        sql += " 	o.CODE = temp_parent_code;                                                                         "
        sql += " END WHILE;                                                                                            "
        sql += " RETURN temp_level_code;                                                                               "
        sql += " END;                                                                              "
        print(sql)
        self.cur.execute(sql)
        self.conn.commit()

        sql = "update v_tbl_func_org o set o.LEVEL_CODE = get_org_level_code(o.`CODE`)"
        self.cur.execute(sql)
        self.conn.commit()

    # 部门  创建函数并更新levelCode
    def updateDeptLevelCode(self):
        sql = "DROP FUNCTION IF EXISTS get_dept_level_code;"
        self.cur.execute(sql)
        self.conn.commit()

        sql = ""
        sql += " CREATE FUNCTION get_dept_level_code ( deptCode VARCHAR ( 256 ) ) RETURNS VARCHAR ( 256 ) BEGIN                  "
        sql += " DECLARE                                                                                                         "
        sql += " 	temp_level_code VARCHAR ( 256 );                                                                             "
        sql += " DECLARE                                                                                                         "
        sql += " 	temp_parent_code VARCHAR ( 256 );                                                                            "
        sql += " SET temp_level_code = CAST( deptCode AS CHAR );                                                                 "
        sql += " SET temp_parent_code = '';                                                                                      "
        sql += " SELECT                                                                                                          "
        sql += " 	o.parent_code INTO temp_parent_code                                                                          "
        sql += " FROM                                                                                                            "
        sql += " 	v_tbl_func_dept o                                                                                            "
        sql += " WHERE                                                                                                           "
        sql += " 	o.CODE = deptCode;                                                                                           "
        sql += " WHILE                                                                                                           "
        sql += " 	temp_parent_code IS NOT NULL DO                                                                              "
        sql += " SET temp_level_code = CONCAT( temp_parent_code, '-', temp_level_code );                                         "
        sql += " SELECT                                                                                                          "
        sql += " 	o.parent_code INTO temp_parent_code                                                                          "
        sql += " FROM                                                                                                            "
        sql += " 	v_tbl_func_dept o                                                                                            "
        sql += " WHERE                                                                                                           "
        sql += " 	o.CODE = temp_parent_code;                                                                                   "
        sql += " END WHILE;                                                                                                      "
        sql += " RETURN temp_level_code;                                                                                         "
        sql += " END;                                                                                                            "
        self.cur.execute(sql)
        self.conn.commit()

        sql = "update v_tbl_func_dept o set o.LEVEL_CODE = get_dept_level_code(o.code)"
        self.cur.execute(sql)
        self.conn.commit()


if __name__ == "__main__":
    os.chdir(os.path.abspath(os.path.dirname(__file__)))

    starttime = datetime.datetime.now()
    print(starttime)

    mysql_class = mysql_class()  # 类初始化
    mysql_class.ljdb()  # 连接MySQL
    # mysql_class.db = 'zhongmian_qc'
    mysql_class.TENANT_CODE = 'T0021'

    # mysql_class.selectData('ZM_TBL_JNT_SEED_COTTON')

    # mysql_class.file_name = '业务单元列表打印模板.xls'
    # mysql_class.db_table = 'v_tbl_func_org'
    # mysql_class.deleteData()  # 删除旧数据
    # mysql_class.getExeclData()  # 数据导入
    # mysql_class.updateOrgLevelCode()  # 更新levelCode列

    # mysql_class.db_table = 'v_tbl_func_dept'
    # mysql_class.deleteData()  # 删除旧数据
    # mysql_class.file_name = '部门1.xls'
    # mysql_class.getExeclData()  # 数据导入
    # mysql_class.file_name = '部门2.xls'
    # mysql_class.getExeclData()  # 数据导入
    # mysql_class.file_name = '部门3.xls'
    # mysql_class.getExeclData()  # 数据导入
    # mysql_class.file_name = '部门4.xls'
    # mysql_class.getExeclData()  # 数据导入
    # mysql_class.file_name = '部门5.xls'
    # mysql_class.getExeclData()  # 数据导入
    # mysql_class.file_name = '部门6.xls'
    # mysql_class.getExeclData()  # 数据导入
    # mysql_class.updateDeptLevelCode()  # 更新levelCode列

    # mysql_class.file_name = '国家地区列表.xls'
    # mysql_class.db_table = 'v_tbl_dd_area'
    # mysql_class.deleteData()  # 删除旧数据
    # mysql_class.getExeclData()  # 数据导入

    # mysql_class.file_name = '客户基本信息列表.xls'
    # mysql_class.file_name2 = '供应商基本信息列表.xls'
    # mysql_class.db_table = 'v_tbl_cust_info'
    # mysql_class.deleteData()  # 删除旧数据
    # mysql_class.getExeclData()  # 数据导入

    # mysql_class.file_name = '仓库_列表.xls'
    # mysql_class.db_table = 'v_tbl_dd_warehouse'
    # mysql_class.deleteData()  # 删除旧数据
    # mysql_class.getExeclData()  # 数据导入

    # mysql_class.file_name = '物料基本分类打印.xls'
    # mysql_class.db_table = 'v_tbl_dd_product_group'
    # mysql_class.deleteData()  # 删除旧数据
    # mysql_class.getExeclData()  # 数据导入

    # mysql_class.db_table = 'v_tbl_dd_product_tax_rate'
    # mysql_class.deleteData()  # 删除旧数据
    # mysql_class.db_table = 'v_tbl_dd_product_item'
    # mysql_class.deleteData()  # 删除旧数据
    # mysql_class.file_name = '物料基本信息列表.xls'
    # mysql_class.db_table = 'v_tbl_dd_product'
    # mysql_class.deleteData()  # 删除旧数据
    # mysql_class.getExeclData()  # 数据导入

    # 弃用
    # mysql_class.db_table = 'V_TBL_FUNC_EMPLOYEE'
    # mysql_class.deleteData()  # 删除旧数据
    # mysql_class.file_name = '人员1.xls'
    # mysql_class.getExeclData()  # 数据导入
    # mysql_class.file_name = '人员2.xls'
    # mysql_class.getExeclData()  # 数据导入
    # mysql_class.file_name = '人员3.xls'
    # mysql_class.getExeclData()  # 数据导入
    # mysql_class.file_name = '人员4.xls'
    # mysql_class.getExeclData()  # 数据导入

    # mysql_class.db_table = 'V_TBL_FUNC_EMPLOYEE'
    # mysql_class.deleteData()  # 删除旧数据
    # mysql_class.file_name = '人员信息导出.xls'
    # mysql_class.getExeclData()  # 数据导入

    # mysql_class.file_name = '用户列表打印模板.xls'
    # mysql_class.db_table = 'V_TBL_FUNC_USER'
    # mysql_class.deleteData()  # 删除旧数据
    # mysql_class.getExeclData()  # 数据导入

    # mysql_class.file_name = '银行档案的列表打印界面.xls'
    # mysql_class.db_table = 'v_tbl_dd_bank'
    # mysql_class.deleteData()  # 删除旧数据
    # mysql_class.getExeclData()  # 数据导入

    # mysql_class.db_table = 'v_tbl_dd_organ_bank_account'
    # mysql_class.deleteData()  # 删除旧数据
    # mysql_class.file_name = '银行账户-列表1.xls'
    # mysql_class.getExeclData()  # 数据导入
    # mysql_class.file_name = '银行账户-列表2.xls'
    # mysql_class.getExeclData()  # 数据导入

    # mysql_class.file_name = '棉副销售主体资格_编码.xls'
    # mysql_class.db_table = 'ZM_TBL_DD_GIN_PLANT_ASS_SET'
    # mysql_class.deleteData()  # 删除旧数据
    # mysql_class.getExeclData()  # 数据导入

    mysql_class.file_name = '供应商美元银行账户批量启用列表.xls'
    mysql_class.db_table = 'v_tbl_cust_bankinfo'
    mysql_class.deleteData()  # 删除旧数据
    mysql_class.getExeclData()  # 数据导入
    mysql_class.file_name = '供应商人民币银行账户批量启用列表.xls'
    mysql_class.getExeclData()  # 数据导入

    # mysql_class.deleteData()  # 删除旧数据
    # mysql_class.getExeclData()  # 数据导入

    endtime = datetime.datetime.now()

    print(endtime)
    print('\n数据处理成功!所用时间为：' + str((endtime - starttime).seconds))
